7.7 CREATE TABLE syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  or    PRIMARY KEY (index_col_name,...)
  or    KEY [index_name] (index_col_name,...)
  or    INDEX [index_name] (index_col_name,...)
  or    UNIQUE [INDEX] [index_name] (index_col_name,...)
  or    [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
            [reference_definition]
  or    CHECK (expr)

type:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  or    SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  or    MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  or    INT[(length)] [UNSIGNED] [ZEROFILL]
  or    INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  or    BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  or    REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  or    DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  or    NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  or    CHAR(length) [BINARY]
  or    VARCHAR(length) [BINARY]
  or    DATE
  or    TIME
  or    TIMESTAMP
  or    DATETIME
  or    TINYBLOB
  or    BLOB
  or    MEDIUMBLOB
  or    LONGBLOB
  or    TINYTEXT
  or    TEXT
  or    MEDIUMTEXT
  or    LONGTEXT
  or    ENUM(value1,value2,value3,...)
  or    SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
	TYPE = {ISAM | MYISAM | HEAP}
or	AUTO_INCREMENT = #
or	AVG_ROW_LENGTH = #
or	CHECKSUM = {0 | 1}
or	COMMENT = "string"
or	MAX_ROWS = #
or	MIN_ROWS = #
or	PACK_KEYS = {0 | 1}
or	PASSWORD = "string"
or	DELAY_KEY_WRITE = {0 | 1}
or      ROW_FORMAT= { default | dynamic | static | compressed }
or	RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#;

select_statement:
	[IGNORE | REPLACE] SELECT ...  (Some legal select statement)

CREATE TABLE creates a table with the given name in the current database. Rules for allowable table names are given in section 7.1.5 Database, table, index, column and alias names. An error occurs if there is no current database or if the table already exists.

In MySQL 3.22 or later, the table name can be specified as db_name.tbl_name. This works whether or not there is a current database.

In MySQL 3.23, you can use the TEMPORARY keyword when you create a table. A temporary table will automatically be deleted if a connection dies and the name is per connection. This means that two different connections can both use the same temporary table name without conflicting with each other or with an existing table of the same name. (The existing table is hidden until the temporary table is deleted).

In MySQL 3.23 or later, you can use the keywords IF NOT EXISTS so that an error does not occur if the table already exists. Note that there is no verification that the table structures are identical.

Each table tbl_name is represented by some files in the database directory. In the case of MyISAM-type tables you will get:

For more information on the properties of the various column types, see section 7.3 Column types.

• If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.
• An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. See section 22.4.29 mysql_insert_id(). If you delete the row containing the maximum value for an AUTO_INCREMENT column, the value will be reused with an ISAM table but not with a MyISAM table. If you delete all rows in the table with DELETE FROM TABLE (without a WHERE), the sequence starts over for both table types. Note: There can be only one AUTO_INCREMENT column per table, and it must be indexed. MySQL 3.23 will also only work properly if the auto_increment column only has positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers 'wrap' over from positive to negative and also to ensure that one doesn't accidently get a auto_increment column that contains 0. To make MySQL compatible with some ODBC applications, you can find the last inserted row with the following query:

  SELECT * FROM tbl_name WHERE auto_col IS NULL
  

• NULL values are handled differently for TIMESTAMP columns than for other column types. You cannot store a literal NULL in a TIMESTAMP column; setting the column to NULL sets it to the current date and time. Because TIMESTAMP columns behave this way, the NULL and NOT NULL attributes do not apply in the normal way and are ignored if you specify them. On the other hand, to make it easier for MySQL clients to use TIMESTAMP columns, the server reports that such columns may be assigned NULL values (which is true), even though TIMESTAMP never actually will contain a NULL value. You can see this when you use DESCRIBE tbl_name to get a description of your table. Note that setting a TIMESTAMP column to 0 is not the same as setting it to NULL, because 0 is a valid TIMESTAMP value.
• If no DEFAULT value is specified for a column, MySQL automatically assigns one. If the column may take NULL as a value, the default value is NULL. If the column is declared as NOT NULL, the default value depends on the column type:
  • For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT column, the default value is the next value in the sequence.
  • For date and time types other than TIMESTAMP, the default is the appropriate ``zero'' value for the type. For the first TIMESTAMP column in a table, the default value is the current date and time. See section 7.3.6 Date and time types.
  • For string types other than ENUM, the default value is the empty string. For ENUM, the default is the first enumeration value.
• KEY is a synonym for INDEX.
• In MySQL, a UNIQUE key can have only distinct values. An error occurs if you try to add a new row with a key that matches an existing row.
• A PRIMARY KEY is a unique KEY with the extra constraint that all key columns must be defined as NOT NULL. In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY.
• A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attibute in a column specification. Doing so will mark only that single column as primary. You must use the PRIMARY KEY(index_col_name, ...) syntax.
• If the PRIMARY or UNIQUE key consists of only one column and this is of type integer, you can also refer to it as _rowid (new in 3.23.11).
• If you don't assign a name to an index, the index will be assigned the same name as the first index_col_name, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. See section 7.23 SHOW syntax (Get information about tables, columns,...).
• Only the MyISAM table type supports indexes on columns that can have NULL values. In other cases you must declare such columns NOT NULL or an error results.
• With col_name(length) syntax, you can specify an index which uses only a part of a CHAR or VARCHAR column. This can make the index file much smaller. See section 7.3.9 Column indexes.
• Only the MyISAM table type supports indexing on BLOB and TEXT columns. When putting an index on a BLOB or TEXT column you MUST always specify the length of the index:

  CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
  

• When you use ORDER BY or GROUP BY with a TEXT or BLOB column, only the first max_sort_length bytes are used. See section 7.3.7.2 The BLOB and TEXT types.
• The FOREIGN KEY, CHECK and REFERENCES clauses don't actually do anything. The syntax for them is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. See section 5.4 Functionality missing from MySQL.
• Each NULL column takes one bit extra, rounded up to the nearest byte.
• The maximum record length in bytes can be calculated as follows:

  row length = 1
               + (sum of column lengths)
               + (number of NULL columns + 7)/8
               + (number of variable-length columns)
  

• The table_options and SELECT options is only implemented in MySQL 3.23 and above. The different table types are:

  ISAM	The original table handler. See section 8.2 ISAM tables.
  MyISAM	The new binary portable table handler. See section 8.1 MyISAM tables.
  HEAP	The data for this table is only stored in memory. See section 8.3 HEAP tables.
  BDB or Berkeley_db	Transaction safe tables See section 8.4 BDB or Berkeley_db tables.

  See section 8 MySQL table types. The other table options are used to optimize the behavior of the table. In most cases, you don't have to specify any of them. The options work for all table types, if not otherwise indicated.

  AUTO_INCREMENT	The next auto_increment value you want to set for your table (MyISAM)
  AVG_ROW_LENGTH	An approximation of the average row length for your table. You only need to set this for tables with variable size records.
  CHECKSUM	Set this to 1 if you want MySQL to maintain a checksum for all rows (makes the table a little slower to update but makes it easier to find corrupted tables) (MyISAM)
  COMMENT	A 60 character comment for your table
  MAX_ROWS	Max number of rows you plan to store in the table
  MIN_ROWS	Minimum number of rows you plan to store in the table
  PACK_KEYS	Set this to 1 if you want to have smaller index. This usually makes updates slower and reads faster (MyISAM, ISAM).
  PASSWORD	Encrypt the .frm file with a password. This option doesn't do anything in the standard MySQL version.
  DELAY_KEY_WRITE	Set this to 1 if want to delay key table updates until the table is closed (MyISAM).
  ROW_FORMAT	Defines how the rows should be stored (for the future).

  When you use a MyISAM table, MySQL uses the product of max_rows * avg_row_length to decide how big the resulting table will be. If you don't specify any of the above options, the maximum size for a table will be 4G (or 2G if your operating systems only supports 2G tables). If you don't use PACK_KEYS, the default is to only pack strings, not numbers. If you use PACK_KEYS=1, numbers will be packed as well. When packing binary number keys, MySQL will use prefix compression. This means that you will only get a big benefit of this if you have many numbers that are the same. Prefix compression means that every key needs one extra byte to indicate how many bytes of the previous key are the same for the next key (note that the pointer to the row is stored in high-byte-first-order directly after the key, to improve compression. This means that if you have many equal keys on two rows in a row, all following 'same' keys will usually only take 2 bytes (including the pointer to the row). Compare this to the ordinary case where the following keys will take 'storage_size_for_key' + pointer_size (usually 4). On the other hand, if all keys are totally different, you will lose 1 byte per key, if the key isn't a key that can have NULL values (In this case the packed key length will be stored in the same byte that is used to mark if a key is NULL).
• If you specify a SELECT after the CREATE STATEMENT, MySQL will create new fields for all elements in the SELECT. For example:

  mysql> CREATE TABLE test (a int not null auto_increment,
             primary key (a), key(b))
             TYPE=HEAP SELECT b,c from test2;
  

  This will create a HEAP table with 3 columns. Note that the table will automatically be deleted if any errors occur while copying data into the table.
• The RAID_TYPE option will help you to break the 2G/4G limit on OSes that don't support big files. You can get also more speed from the I/O bottleneck by putting RAID directories on different physical disks. RAID_TYPE will work on any OS, as long as you have configured MySQL with --with-raid. For now the only allowed RAID_TYPE is STRIPED (1 and RAID0 are aliases for this). If you specify RAID_TYPE=STRIPED for a MyISAM table, MyISAM will create RAID_CHUNKS sub-directories named 00, 01, 02 in the database directory. In each of these directories MyISAM will create an table_name.MYD. When writing data to the data file, the RAID handler will map the first RAID_CHUNKSIZE *1024 bytes to the first file, the next RAID_CHUNKSIZE *1024 bytes to the next file and so on.

• 7.7.1 Silent column specification changes